package br.com.sistelecom.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

import br.com.sistelecom.connection.SistelecomSingleConnection;
import br.com.sistelecom.entidade.relatorio.FuncionarioRelatorio;
import br.com.sistelecom.entity.Funcionario;
import br.com.sistelecom.to.FuncionarioTO;

public class FuncionarioDAOImpl implements DAO<Funcionario>{

	public void salvar(Funcionario funcionario) throws Exception {
		try{
			Connection conn = SistelecomSingleConnection.getConnection();

			String SQL = "INSERT INTO funcionario (cpf, departamento, nome, logradouro, numero, complemento, bairro, cidade, uf, cep, nasc, tel_1, tel_2, email, doc, tipo_doc, cargo, superv_funcao,"
					+ " superv_nome, admissao, status, login, password) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

			PreparedStatement ps = conn.prepareStatement(SQL);
			ps.setString(1, funcionario.getCpf());
			ps.setInt(2, funcionario.getDepartamento());
			ps.setString(3, funcionario.getNome());
			ps.setString(4, funcionario.getLogradouro());
			ps.setInt(5, funcionario.getNumero());
			ps.setString(6, funcionario.getComplemento());
			ps.setString(7, funcionario.getBairro());
			ps.setInt(8, funcionario.getCidade());
			ps.setInt(9, funcionario.getUf());
			ps.setLong(10, funcionario.getCep());
			ps.setDate(11, new java.sql.Date(funcionario.getNasc().getTime()));
			ps.setLong(12, funcionario.getTel1());
			ps.setLong(13, funcionario.getTel2());
			ps.setString(14, funcionario.getEmail());
			ps.setLong(15, funcionario.getDoc());
			ps.setString(16, funcionario.getTipoDoc());
			ps.setInt(17, funcionario.getCargo());
			ps.setInt(18, funcionario.getSupervFuncao());
			ps.setString(19, funcionario.getSupervNome());
			ps.setDate(20, new java.sql.Date(funcionario.getAdmissao().getTime()));
			ps.setString(21, funcionario.getStatus());
			ps.setString(22, funcionario.getLogin());
			ps.setString(23, funcionario.getPassword());
			ps.executeUpdate();

		} catch (Exception e) {
			throw new Exception();
		} 
	}

	public void atualizar(Funcionario funcionario) throws Exception{
		try{
			Connection conn = SistelecomSingleConnection.getConnection();

			String SQL = "UPDATE funcionario SET cpf=?, departamento=?, nome=?, logradouro=?, numero=?, complemento=?, " +
					"bairro=?, cidade=?, uf=?, cep=?, nasc=?, tel_1=?, tel_2=?, email=?, doc=?, tipo_doc=?, cargo=?, superv_funcao=?, superv_nome=?, " +
					"admissao=?, status=?, login=?, password=? " +
					"where idfuncionario = ?;";

			PreparedStatement ps = conn.prepareStatement(SQL);
			ps.setString(1, funcionario.getCpf());
			ps.setInt(2, funcionario.getDepartamento());
			ps.setString(3, funcionario.getNome());
			ps.setString(4, funcionario.getLogradouro());
			ps.setInt(5, funcionario.getNumero());
			ps.setString(6, funcionario.getComplemento());
			ps.setString(7, funcionario.getBairro());
			ps.setInt(8, funcionario.getCidade());
			ps.setInt(9, funcionario.getUf());
			ps.setLong(10, funcionario.getCep());
			ps.setDate(11, new java.sql.Date(funcionario.getNasc().getTime()));
			ps.setLong(12, funcionario.getTel1());
			ps.setLong(13, funcionario.getTel2());
			ps.setString(14, funcionario.getEmail());
			ps.setLong(15, funcionario.getDoc());
			ps.setString(16, funcionario.getTipoDoc());
			ps.setInt(17, funcionario.getCargo());
			ps.setInt(18, funcionario.getSupervFuncao());
			ps.setString(19, funcionario.getSupervNome());
			ps.setDate(20, new java.sql.Date(funcionario.getAdmissao().getTime()));
			ps.setString(21, funcionario.getStatus());
			ps.setString(22, funcionario.getLogin());
			ps.setString(23, funcionario.getPassword());
			ps.setInt(24, funcionario.getIdFuncionario());
			ps.executeUpdate();

		} catch (Exception e) {
			throw new Exception();
		}
	}

	public List<Funcionario> listarTodos(){
		PreparedStatement ps = null;
		Connection conn = SistelecomSingleConnection.getConnection();
		ResultSet rs = null;

		try{
			ps = conn.prepareStatement("select * from funcionario");
			rs = ps.executeQuery();
			List<Funcionario> list = new ArrayList<Funcionario>();
			while(rs.next()) {
				list.add(new Funcionario(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getString(4), rs.getString(5), rs.getInt(6), rs.getString(7), rs.getString(8), rs.getInt(9), rs.getInt(10), rs.getLong(11), rs.getDate(12), rs.getLong(13), rs.getLong(14), rs.getString(15), rs.getLong(16), rs.getString(17), rs.getInt(18), rs.getInt(19), rs.getString(20), rs.getDate(21), rs.getString(22), rs.getString(23), rs.getString(24)));
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	public List<FuncionarioTO> todosFuncionariosParaExibirEmTabela() {

		PreparedStatement ps = null;
		Connection conn = SistelecomSingleConnection.getConnection();
		ResultSet rs = null;

		try{
			ps = conn.prepareStatement("select * from funcionario");
			rs = ps.executeQuery();
			List<FuncionarioTO> list = new ArrayList<FuncionarioTO>();
			while(rs.next()) {
				Integer idFuncionario = rs.getInt(1);
				String cpf = rs.getString(2);
				int departamento = rs.getInt(3);
				String nomeFuncionario = rs.getString(4);


				list.add(new FuncionarioTO(Boolean.FALSE, idFuncionario, cpf, departamento, null, nomeFuncionario));
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	public Funcionario obterPorId(int id){

		PreparedStatement ps = null;
		Connection conn = SistelecomSingleConnection.getConnection();
		ResultSet rs = null;

		try {
			ps = conn.prepareStatement("select * from funcionario where idfuncionario = ?");
			ps.setInt(1, id);
			rs = ps.executeQuery();
			if (!rs.next()) {
				throw new Exception("Não foi encontrado o funcionario com esse id: " + id);
			}

			Funcionario funcionario = new Funcionario();
			funcionario.setIdFuncionario(id);
			funcionario.setCpf(rs.getString(2));
			funcionario.setDepartamento(rs.getInt(3));
			funcionario.setNome(rs.getString(4));
			funcionario.setLogradouro(rs.getString(5));
			funcionario.setNumero(rs.getInt(6));
			funcionario.setComplemento(rs.getString(7));
			funcionario.setBairro(rs.getString(8));
			funcionario.setCidade(rs.getInt(9));
			funcionario.setUf(rs.getInt(10));
			funcionario.setCep(rs.getLong(11));
			funcionario.setNasc(rs.getDate(12));
			funcionario.setTel1(rs.getLong(13));
			funcionario.setTel2(rs.getLong(14));
			funcionario.setEmail(rs.getString(15));
			funcionario.setDoc(rs.getLong(16));
			funcionario.setTipoDoc(rs.getString(17));
			funcionario.setCargo(rs.getInt(18));
			funcionario.setSupervFuncao(rs.getInt(19));
			funcionario.setSupervNome(rs.getString(20));
			funcionario.setAdmissao(rs.getDate(21));
			funcionario.setStatus(rs.getString(22));
			funcionario.setLogin(rs.getString(23));
			funcionario.setPassword(rs.getString(24));

			return funcionario;
		} catch (Exception e) {
			e.printStackTrace();
		} 
		return null;
	}

	public void excluir(Funcionario funcionario) throws Exception {
		try{
			Connection conn = SistelecomSingleConnection.getConnection();

			String SQL = "delete from funcionario where idfuncionario = ?";

			PreparedStatement ps = conn.prepareStatement(SQL);
			ps.setInt(1, funcionario.getIdFuncionario());
			ps.executeUpdate();

		} catch (Exception e) {
			throw new Exception();
		}
	}

	public List<Funcionario> buscarFuncionarioComCargoConsultorComercial(final int idCargo){

		String sql = "select f.idfuncionario, f.nome from funcionario as f ";
		sql += "inner join cargo as c ";
		sql += "on f.cargo = c.idcargo ";
		sql += "where c.idcargo = ?";

		PreparedStatement ps = null;
		Connection conn = SistelecomSingleConnection.getConnection();
		ResultSet rs = null;

		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, idCargo);

			rs = ps.executeQuery();

			List<Funcionario> lista = new LinkedList<Funcionario>();

			while (rs.next()) {
				Funcionario funcionario = new Funcionario();
				funcionario.setIdFuncionario(rs.getInt("idfuncionario"));
				funcionario.setNome(rs.getString("nome"));

				lista.add(funcionario);
			}

			return lista;
		} catch (Exception e) {
			e.printStackTrace();
		} 
		return null;
	}

	public List<FuncionarioRelatorio> listarParaRelatorio(){

		PreparedStatement ps = null;
		Connection conn = SistelecomSingleConnection.getConnection();
		ResultSet rs = null;

		try{
			ps = conn.prepareStatement("select * from funcionario");
			rs = ps.executeQuery();
			List<FuncionarioRelatorio> listRelatorio = new ArrayList<FuncionarioRelatorio>();
			while(rs.next()) {

				int idFuncionario = rs.getInt(1);
				String nome = rs.getString(4);
				long tel1 = rs.getLong(13);
				String email = rs.getString(15);
				Date admissao = rs.getDate(21);
				String status = rs.getString(22);
				String login = rs.getString(23);

				listRelatorio.add(new FuncionarioRelatorio(idFuncionario, nome, tel1, email, admissao, status, login));
			}
			return listRelatorio;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	public Boolean validarLogin(final String nome, final String senha){

		Connection conn = SistelecomSingleConnection.getConnection();

		try {
			PreparedStatement ps = conn.prepareStatement("select login, password from funcionario where login = ? and password = ?;");
			ps.setString(1, nome);
			ps.setString(2, senha);

			ResultSet rs = ps.executeQuery();

			if (rs.next()) {
				return Boolean.TRUE;
			}else{
				return Boolean.FALSE;
			}
		}catch(Exception e){}

		return Boolean.FALSE;
	}
}
